﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FW;
using dbiparser;

namespace Kcadastro.classes
{
    public class bill
    {
        private fwlog log = (fwlog)ConfigurationManager.GetSection("fwlog");

        kcadDataContext db = new kcadDataContext(ConfigurationManager.ConnectionStrings["KcadConnectionString"].ToString());

        private string _msg;
        public string MSG { get { return _msg; } }

        public bill()
        { }

        public List<int> verificaAnos()
        {
            List<int> result = new List<int>();
            var res = (from tb in db.tb_bills
                           select new {
                           ano = tb.dt_bill.Value.Year
                           }).Distinct();
            foreach (var a in res)
            {
                result.Add(Convert.ToInt16(a.ano));
            }

            return result;

        }

        public List<BillRelat> listarFechMes(int _ano, int _mes)
        {
            List<BillRelat> ret = (from tb in db.tb_bills
                                   from tbe in db.tb_empresas
                                   where tb.dt_bill.Value.Month == _mes && tb.dt_bill.Value.Year == _ano
                                   && tbe.PK_IDemp == tb.FK_IDemp
                                   select new BillRelat { 
                                    DTFECH = (DateTime)tb.dt_bill,
                                    EMP = tbe.RS,
                                    FKEMP = (int) tb.FK_IDemp,
                                    PKBILL = tb.PK_IDbill,
                                    QTDOSS = tb.qtdOSs == null ? 0: (int)tb.qtdOSs,
                                    QTDREGS = tb.qtdRegistros == null ? 0: (long)tb.qtdRegistros,
                                    VALOR = tb.valorTotal == null? 0: (decimal) tb.valorTotal
                                   }
                                   ).ToList<BillRelat>();

            return ret;
        }

        private void updateProcessados()
        {
            var tos = (from tb in db.tb_os
                       where tb.stts == 10
                       select tb);
            foreach (tb_o to in tos)
            {
                tb_o toupt = db.tb_os.Single(t => t.PK_IDos == to.PK_IDos);
                toupt.qtd_processada = db.tb_os_resumos.Single(t => t.FK_IDos == to.PK_IDos).qtdprocessados;
                db.SubmitChanges();

                if (to.tpps == 3)
                {
                    // PJ
                    tb_osXserv tb_serv = db.tb_osXservs.Single(t => t.FK_IDos == to.PK_IDos && t.FK_IDserv == 1);
                    tb_serv.qtdprocessada = (int)toupt.qtd_processada;
                    tb_serv.valorFechado = ValorProcessados(1, (int)toupt.qtd_processada);
                    db.SubmitChanges();
                }
                else
                { 
                    // PF
                    tb_osXserv tb_serv = db.tb_osXservs.Single(t => t.FK_IDos == to.PK_IDos && t.FK_IDserv == 4);
                    tb_serv.qtdprocessada = (int)toupt.qtd_processada;
                    tb_serv.valorFechado = ValorProcessados(4, (int)toupt.qtd_processada);
                    db.SubmitChanges();
                }
            }


        }

        private decimal ValorProcessados(int _idserv, int _qtd)
        {
            decimal ret = 0;

            decimal preco = 0;
            tb_preco tp = db.tb_precos.Single(t => t.FK_IDserv == _idserv);

            if (_qtd > tp.RegMinimo)
            {
                preco = (decimal)tp.franquia + ((decimal)(_qtd - (int)tp.RegMinimo) * (decimal)tp.valorporregistro);
                //preco = (decimal) db.tb_precos.Single(t => t.FK_IDserv == tos.FK_IDserv && t.qtdini <= tos.qtdregistros && t.qtdfim >= tos.qtdregistros).valorporregistro;
            }
            else
            {
                preco = (decimal)tp.franquia;
            }
                
            ret = preco;

            return ret;
        }

        public void fecharPeriodo(int _ano, int _mes)
        {
            // volta status das oss para 10
            var ot = (from tb in db.tb_bills where tb.dt_bill == new DateTime(_ano, _mes, 1).AddMonths(1).AddDays(-1) select tb);
            foreach (var b in ot)
            {
                var otd = (from tb in db.tb_billdets where tb.FK_idbill == b.PK_IDbill select tb);
                foreach (var dbill in otd)
                {
                    if (dbill.FK_IDos != 0)
                    {
                        tb_o tosu = db.tb_os.Single(t => t.PK_IDos == dbill.FK_IDos);
                        tosu.stts = 10;
                        tosu.status = Enum.GetName(typeof(STTS), 10);
                        db.SubmitChanges();
                    }
                }
            }

            db.ExecuteCommand("delete from tb_bill where dt_bill >= {0}",  new DateTime(_ano, _mes, 1).AddMonths(1).AddDays(-1));
            db.SubmitChanges();

            updateProcessados();


            var tempr = (from tb in db.tb_empresas
                         select tb);
            


            foreach (tb_empresa te in tempr)
            {
                tb_bill bil = new tb_bill();
                bil.FK_IDemp = te.PK_IDemp;
                bil.dt_bill = new DateTime(_ano, _mes, 1).AddMonths(1).AddDays(-1);
                bil.dt_reg = DateTime.Now;

                db.tb_bills.InsertOnSubmit(bil);
                db.SubmitChanges();

                var tos = (from tb in db.tb_os
                           where tb.stts == 10
                           && tb.FK_IDemp == te.PK_IDemp
                           select tb);
                foreach (tb_o os in tos)
                {
                    tb_billdet ndet = new tb_billdet();
                    ndet.FK_idbill = bil.PK_IDbill;
                    ndet.cupom = "";
                    ndet.desconto = te.desconto;
                    ndet.dt_inicio = os.dt_reg;
                    ndet.dt_fim = os.dt_termino;
                    ndet.FK_IDcupom = 0;
                    ndet.FK_IDemp = te.PK_IDemp;
                    ndet.FK_IDos = os.PK_IDos;
                    ndet.nomeOS = os.nomeOS;
                    ndet.qtdRegistros = (int) os.qtd_processada;
                    ndet.tpps = os.tpps;
                    if (os.tpps == 3)
                    {
                        ndet.valor = ValorProcessados(1, (int)os.qtd_processada);
                    }
                    else
                    {
                        ndet.valor = ValorProcessados(4, (int)os.qtd_processada);
                    }
                    db.tb_billdets.InsertOnSubmit(ndet);
                    db.SubmitChanges();

                    // update OS para status computada.
                    tb_o tou = db.tb_os.Single(t => t.PK_IDos == os.PK_IDos);
                    tou.stts = 11;
                    tou.status = Enum.GetName(typeof(STTS), 11);
                    db.SubmitChanges();

                }
                // insere dados das consultas unitárias
                var tcuni = (from tb in db.tb_cunis
                             where tb.FK_IDemp == te.PK_IDemp
                             && tb.DataCuni.Value.Month == _mes
                             && tb.DataCuni.Value.Year == _ano
                             select tb.PK_IDcuni).Count();
                if (tcuni != null)
                {
                    tb_billdet ndetcuni = new tb_billdet();
                    ndetcuni.FK_idbill = bil.PK_IDbill;
                    ndetcuni.cupom = "";
                    ndetcuni.desconto = te.desconto;
                    ndetcuni.dt_inicio = new DateTime(_ano, _mes, 1);
                    ndetcuni.dt_fim = new DateTime(_ano, _mes, 1).AddMonths(1).AddDays(-1);
                    ndetcuni.FK_IDcupom = 0;
                    ndetcuni.FK_IDemp = te.PK_IDemp;
                    ndetcuni.FK_IDos = 0;
                    ndetcuni.nomeOS = "Consultas Unitárias";
                    ndetcuni.qtdRegistros = (int) tcuni;
                    ndetcuni.tpps = 3;
                    ndetcuni.valor = ValorProcessados(1, (int) tcuni);
                    db.tb_billdets.InsertOnSubmit(ndetcuni);
                    db.SubmitChanges();

                }
                // fim cuni

                var detalhes = (from tb in db.tb_billdets
                                where tb.FK_idbill == bil.PK_IDbill
                                select tb);

                bil.qtdOSs = detalhes.Count<tb_billdet>();
                bil.qtdRegistros = detalhes.Sum<tb_billdet>(t => t.qtdRegistros);
                if (te.contratoTipo == 2)
                {
                    bil.valorTotal = detalhes.Sum<tb_billdet>(t => t.valor);
                }
                else
                {
                    bil.valorTotal = te.contratoValor;
                }
                db.SubmitChanges();
            }
        }

        public List<tb_billdet> listaDeOSs(long __idbill)
        {
            List<tb_billdet> ret = new List<tb_billdet>();
            ret = (from tb in db.tb_billdets
                   where tb.FK_idbill == __idbill
                   select tb).ToList<tb_billdet>();
            return ret;
        }

        public BillRelat billDados(long __idbill)
        {
            BillRelat ret = new BillRelat();
            var tt = (from tb in db.tb_bills
                                   from tbe in db.tb_empresas
                                   where tb.PK_IDbill == __idbill
                                   && tbe.PK_IDemp == tb.FK_IDemp
                                   select new BillRelat { 
                                    DTFECH = (DateTime)tb.dt_bill,
                                    EMP = tbe.RS,
                                    FKEMP = (int) tb.FK_IDemp,
                                    PKBILL = tb.PK_IDbill,
                                    QTDOSS = tb.qtdOSs == null ? 0: (int)tb.qtdOSs,
                                    QTDREGS = tb.qtdRegistros == null ? 0: (long)tb.qtdRegistros,
                                    VALOR = tb.valorTotal == null? 0: (decimal) tb.valorTotal
                                   }
                                   ).ToList<BillRelat>();

            foreach (BillRelat t in tt)
            {
                ret.DTFECH = t.DTFECH;
                ret.EMP = t.EMP;
                ret.FKEMP = t.FKEMP;
                ret.PKBILL = t.PKBILL;
                ret.QTDOSS = t.QTDOSS;
                ret.QTDREGS = t.QTDREGS;
                ret.VALOR = t.VALOR;
            }

            return ret;
        }
    }

    public class BillRelat
    {
        public long PKBILL { get; set; }
        public int FKEMP { get; set; }
        public string EMP { get; set; }
        public DateTime DTFECH { get; set; }
        public int QTDOSS { get; set; }
        public long QTDREGS { get; set; }
        public decimal VALOR { get; set; }
    }
}